<?php

/**
 * This is the model class for table "organizations".
 *
 * The followings are the available columns in table 'organizations':
 * @property string $organizationID
 * @property string $organizationName
 * @property string $address1
 * @property string $address2
 * @property string $city
 * @property string $state
 * @property string $zip
 * @property string $organizationType
 */
class Organizations extends CActiveRecord
{ 	
	/**
	 * Returns the static model of the specified AR class.
	 * @return Organizations the static model class
	 */
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}

	/**
	 * @return string the associated database table name
	 */
	public function tableName()
	{
		return 'organizations';
	}

	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			array('organization_name, address1, city, state, zip, organization_type_ID, phone_number, email_address, contact_first_name, contact_last_name', 'required','on'=>'register'),
			array('organization_name, address1, city, state, zip, organization_type_ID, phone_number, email_address, contact_first_name, contact_last_name', 'required' ,'on'=>'update'),
			array('organization_name, DBA, address1, address2, email_address', 'length', 'max'=>50,'on'=>'update, register'),
			array('phone_number, contact_first_name, contact_last_name', 'length', 'max'=>20,'on'=>'update, register'),
			array('organization_name', 'unique', 'criteria' => array('select'=>Organizations::model()->tableName(),
															'condition' => 'binary organization_name=:organization_name',
															'params'=>array(':organization_name'=>$this->organization_name)), 
															'message' => 'this organization doesn\'t exist, please fill out the information and create a new one','on'=>'register'),
			array('organization_name', 'unique', 'criteria' => array('select'=>Users::model()->tableName(),
															'condition' => 'binary organization_name=:organization_name and organization_ID!=:organization_ID',
															'params'=>array(':organization_name'=>$this->organization_name, ':organization_ID'=>$this->organization_ID)), 'on'=>'update'),	
			array('fed_ID', 'unique', 'criteria' => array('select'=>Organizations::model()->tableName(),
															'condition' => 'fed_ID=:fed_ID',
															'params'=>array(':fed_ID'=>$this->fed_ID)), 'on'=>'register'
															),
			array('fed_ID', 'unique', 'criteria' => array('select'=>Organizations::model()->tableName(),
															'condition' => 'fed_ID=:fed_ID and organization_ID!=:organization_ID',
															'params'=>array(':fed_ID'=>$this->fed_ID, ':organization_ID'=>$this->organization_ID)), 'on'=>'update'
															),
			array('fed_ID', 'numerical', 'integerOnly'=>true, 'on'=>'register, update'),
			array('city', 'length', 'max'=>30,'on'=>'update, register'),
			array('state', 'length', 'max'=>2,'on'=>'update, register'),
			array('zip', 'length', 'max'=>5,'on'=>'update, register'),
			array('zip', 'match', 'pattern'=>'/^\d{5}|\d{5}-\d{4}$/', 'message'=>'the organization\'s zip code format is not correct','on'=>'update, register'),
		);
	}

	/**
	 * @return array relational rules.
	 */
	public function relations()
	{
		return array(
			'users'=>array(self::HAS_MANY, 'Users', 'organization_ID'),
			'orgtype'=>array(self::BELONGS_TO, 'OrganizationTypes', 'organization_type_ID'),
			'orgProRelation'=>array(self::HAS_MANY, 'OrgProductRelation', 'organization_ID'),
			'bankAccounts'=>array(self::HAS_MANY, 'BankAccounts', 'organization_ID'),
			'orgRackRelation'=>array(self::HAS_MANY, 'OrgRackRelation', 'organization_ID'),
		);
	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
		return array(
			'organization_ID' => 'Organization',
			'organization_name' => 'Organization Name',
			'address1' => 'Billing Address',
			'address2' => 'Address2',
			'city' => 'City',
			'state' => 'State',
			'zip' => 'Zip',
			'DBA' => 'DBA',
			'fed_ID' => 'FED ID',
			'organization_type_ID' => 'Organization Type',
		);
	}
	
	public function searchOrgID($orgName)
	{
		$connection = Yii::app()->db;
		$sql = "SELECT organization_ID FROM ".Organizations::model()->tableName()." WHERE organization_name = '$orgName'";
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		$row = $dataReader->read();
		return $row['organization_ID'];
	}
	
	public function getOrgInfo($orgName)
	{
		$res=array();
		if($orgName!=null)
		{
			$connection = Yii::app()->db;
			$sql = "SELECT * FROM ".$this->tableName()." WHERE organization_name='$orgName'";
			$command = $connection->createCommand($sql);
			$dataReader = $command->query();
			foreach($dataReader as $row)
			{
				$res[]=$row['DBA'];					
				$res[]=$row['fed_ID'];
				$res[]=$row['address1'];
				$res[]=$row['city'];
				$res[]=$row['state'];
				$res[]=$row['zip'];
				$res[]=$row['organization_type_ID'];
				$sql = "SELECT product_ID FROM ".OrgProductRelation::model()->tableName(). " WHERE organization_ID=".$row['organization_ID'];
				$command = $connection->createCommand($sql);
				$reader = $command->query();
				$orgproduct = array();
				foreach($reader as $r)
				{
					$orgproduct[]=$r['product_ID'];
				}
				$res[]=$orgproduct;
				return $res;
			}
		}
		return $res;
	}
	
	public function searchOrgName($orgName)
	{
		$connection = Yii::app()->db;
		$sql = "SELECT * FROM ".Organizations::model()->tableName()." WHERE organization_name like '$orgName%'";
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		$names=array();
		foreach($dataReader as $row)
		{
			$names[]=array(
						'organization_ID'=>$row['organization_ID'],
						'organization_name'=>$row['organization_name'],
					);
		}
		return $names;
	}
	
	public function getOrganization($userID)
	{
		$organizationID = Users::model()->getOrganizationID($userID);
		$sql = "SELECT organization_name, email_address, phone_number, address1, address2, contact_first_name, contact_last_name FROM ".$this->tableName()." where organization_ID='".$organizationID."'";
		$resultset = $this->findAllBySql($sql);
			
		return $resultset;
	}
	
	public function getOrganizationName($userID)
	{
		$organizationID = Users::model()->getOrganizationID($userID);
		$sql = "SELECT organization_name FROM ".$this->tableName()." where organization_ID='".$organizationID."'";
		$resultset = $this->findAllBySql($sql);
		
		foreach ($resultset as $row) {
			$organizationName = $row->organization_name;
		}
			
		return $organizationName;
	}
}